<?php

    define("MYSQL_DATETIME_FORMAT","YYYY-MM-dd HH:mm:ss");

    class Model_Worktime extends Model_Base  {
	
        private $table_names = array(0=>"person",
                                     1=>"worktime",
                                     2=>"worktime_series");
        protected $_db;

        public function init() 
        {
            $this->_db=&$this->db;
        }

        public function listWorkers() 
        {

            $select = $this->db->select()
                      ->from($this->table_names[0])
                      ->where("person_type='Mitarbeiter'")
                      ->where("(expire_date is null OR expire_date >= now())")
                      ->order("surname ASC")
                      ->order("forename ASC");


            //print $select->__toString();
            $result = $this->db->fetchAll($select);

            return $result;
        }

        public function getWorkerById($pid) {
            $select = $this->db->select()
                      ->from($this->table_names[0])
                      ->where('id = ?', $pid);
            return $this->db->fetchRow($select);            
        }
        
        public function getWorkerIdByWorktimeId($wid) {
            $sql = "SELECT a.idperson AS idp FROM ".$this->table_names[1]." AS a WHERE a.id='".$wid."';";
            $stmt = $this->db->query($sql);
            $c = $stmt->fetch();
            return strval($c['idp']);            
        }

        public function getWorkTimeSerieId($id) {
            $sql = "SELECT a.idworktime_series AS idws FROM ".$this->table_names[1]." AS a WHERE a.id='".$id."';";
            $stmt = $this->db->query($sql);
            $c = $stmt->fetch();
            return strval($c['idws']);
        }

        public function swapDayMonth($a) {
            $tmp = $a->get(Zend_Date::MONTH);
            $a->setMonth($a->get(Zend_Date::DAY));
            $a->setDay($tmp);
            return $a;
        }
        
        private function addEndtimeInHmToDate($d) {
            return $d." 23:59";
        }
        
	    // get date and time values out of mysql datetime string
        public function get_datetime_from_mysql($str,$flag) 
        {
	        $str0="";
	        if(strlen($str)) {
	            switch($flag) {
		        case "Y":
		            $str0=substr($str,0,4);
		            break;
		        case "m":
	         	    $str0=substr($str,5,2);
		            break;
		        case "d":
		            $str0=substr($str,8,2);
		            break;
		        case "H":
		            $str0=substr($str,11,2);
		            break;
		        case "i":
		            $str0=substr($str,14,2);
		            break;
		        case "s":
		            $str0=substr($str,17,2);
		            break;
		        default:
		            break;
	            }
	        }
	        return $str0;
        }
	
        // convert mysql datetime format to => dd.MM.yyyy HH:mm
        public function get_worker_dt_from_mysql_dt($dt)
        {
	        $str_dt="";
	        if(strlen($dt)>0) {
	            $str_dt=sprintf("%02s.%02s.%04s %02s:%02s",
			            $this->get_datetime_from_mysql($dt,'d'),
			            $this->get_datetime_from_mysql($dt,'m'),
			            $this->get_datetime_from_mysql($dt,'Y'),
			            $this->get_datetime_from_mysql($dt,'H'),
			            $this->get_datetime_from_mysql($dt,'i'));
            }
	        return $str_dt;
        }
	
        public function addWorkTime($b=array(),$bSerie=false) 
        {
            $entry_type = "";
            $df="";
            $dt="";
            
            if(count($b) > 0) 
            {
                $date_from = new Zend_Date($b['date_from'],'dd.MM.yyyy HH:mm:ss');
                $df = $date_from->toString(MYSQL_DATETIME_FORMAT);

                $date_to = new Zend_Date($b['date_to'],'dd.MM.yyyy HH:mm:ss');
                $dt = $date_to->toString(MYSQL_DATETIME_FORMAT);
                
         
                // determine type of the entry
                $entry_type = "";
                if($b['modus']=="wt")
                    $entry_type = "worktime";
                else if($b['modus']=="av")
                    $entry_type = "availability";
                else
                    $entry_type = "worktime";
                

                if(!$bSerie) {

                    $sql = "INSERT INTO ".$this->table_names[1]." (idperson,date_from,date_to,description,worktime_type,date_create) 
                                            values('".$b['Mitarbeiter']."',
                                               '".$df."',
                                               '".$dt."',
                                               '".mysql_real_escape_string($b['description'])."',
                                               '".$entry_type."',
                                               now());";
                    $this->db->query($sql);

                } else if($bSerie) {
                
                
                    $date_serie = new Zend_Date($this->addEndtimeInHmToDate($b['expire_date']),'dd.MM.yyyy HH:mm:ss');
                    $date_serie->set(23,Zend_Date::HOUR);
                    $date_serie->set(59,Zend_Date::MINUTE);
                    $date_serie->set(59,Zend_Date::SECOND);

                    $unix_timestamp_ds = $this->get_unixtime_from_mysql_dt($date_serie->toString(MYSQL_DATETIME_FORMAT));

                    // lets get a valid serial id from worktime_series table
                    $sql = "INSERT INTO ".$this->table_names[2]." (date_create) values(now());";
                    $this->db->query($sql);

                    $sql = "SELECT max(id) as id FROM ".$this->table_names[2];
                    $stmt = $this->db->query($sql);
                    $c = $stmt->fetch();
                    $idSerie = $c['id'];
    

                    $day_steps = 0;
                    
                    switch($b['once_serie']) {
                        case 'x':
                            $day_steps = $b['x_days']; // x days steps for worktimes
                            break;
                        case 's':
                        default:
                            $day_steps = 7; // 1 week (7 days) steps for worktimes
                            break;                        
                    }
                   

                    /* add the value of day_steps to the day of each date and convert it
                       to mysql datetime format */
                       
                    $bReached = false;
                    
                    while(!$bReached) {

                        $df = $date_from->toString(MYSQL_DATETIME_FORMAT);
                        $dt = $date_to->toString(MYSQL_DATETIME_FORMAT);
                        
                        if($this->get_unixtime_from_mysql_dt($df) > $unix_timestamp_ds) {
                            $bReached |= true;
                            continue;
                        }

                        $sql = "INSERT INTO ".$this->table_names[1]." (idperson,date_from,date_to,description,idworktime_series,worktime_type,date_create) 
                                                values('".$b['Mitarbeiter']."',
                                                       '".$df."',
                                                       '".$dt."',
                                                       '".mysql_real_escape_string($b['description'])."',
                                                       '".$idSerie."',
                                                       '".$entry_type."',                                                           
                                                       now());";
                                                       
                        $this->db->query($sql);

                        $date_from->addDay($day_steps);
                        $date_to->addDay($day_steps);
                    }
 
                }
            }
            return;
        }

        public function updateWorkTime($id,$b=array()) 
        {
            if(count($b)>0 && $id>=0) 
            {   
                $date_from = new Zend_Date($b['date_from'],'dd.MM.yyyy HH:mm');
                //$date_from = $this->swapDayMonth($date_from);
                $df = $date_from->toString(MYSQL_DATETIME_FORMAT);
	
                $date_to = new Zend_Date($b['date_to'],'dd.MM.yyyy HH:mm');
                //$date_to = $this->swapDayMonth($date_to);
                $dt = $date_to->toString(MYSQL_DATETIME_FORMAT);

                $sql = "UPDATE %s SET idperson='%s', 
                              date_from='%s',
                              date_to='%s',
                              description='%s'

                              WHERE id='%d'";

                $sql = sprintf($sql,$this->table_names[1],$b['Mitarbeiter'],$df,$dt,mysql_real_escape_string($b['description']),$id);
                $this->db->query($sql);
            }
        }
        
        protected function generateTimeExistenceQuery($t,$df,$dt,$entry_type,$b,$bCollision)
        {
            $sql = "";
            
            if(!$bCollision) {
            
                $sql = "SELECT count(*) as ne FROM ".$t." 
                        WHERE ((date_from <= '".$df."' AND date_to > '".$dt."') OR
                              (date_from < '".$df."' AND date_to >= '".$dt."') OR
                              (date_from = '".$df."' AND date_to = '".$dt."'))";  
                              
            } else {

                // this additional subquery to the main query gives the full collision 
                
                //$subQuery = " OR (date_to >= '".$df."' AND date_from < '".$dt."') ";
                              //OR (date_to > '".$df."' AND date_from <= '".$dt."')";
                                             
                //$sql = sprintf($sql,$subQuery);
                
                $sql = "SELECT count(*) as ne FROM ".$t." 
                        WHERE ((date_to > '".$df."' AND date_from < '".$dt."') OR
                              (date_from = '".$df."' AND date_to = '".$dt."'))";                  
                
            }
            
            $sql .= " AND worktime_type='".$entry_type."'";
            $sql .= " AND idperson ='".$b['Mitarbeiter']."'";
            
            if(isset($b['id_update'])/* && $entry_type=="worktime"*/)
                $sql .= " AND id!='".$b['id_update']."'";
            
            return $sql;         
            
        }
        
        protected function generateTimeExistenceQueryForDeletion($t,$df,$dt,$entry_type,$b)
        {
            $sql = "";
            
            $sql = "SELECT count(*) as ne FROM ".$t." 
                    WHERE ((date_from >= '".$df."' AND date_to < '".$dt."') OR
                          (date_from > '".$df."' AND date_to <= '".$dt."') OR
                          (date_from = '".$df."' AND date_to = '".$dt."'))";  
            
            $sql .= " AND worktime_type='".$entry_type."'";
            $sql .= " AND idperson ='".$b['Mitarbeiter']."'";
            
            return $sql;         
            
        }        
        
        protected function getAvailabilityWorktimesQueryForDeletion($t,$df,$dt,$entry_type,$b)
        {
            $sql = "";
            
            $sql = "SELECT * FROM ".$t." 
                    WHERE ((date_from >= '".$df."' AND date_to < '".$dt."') OR
                          (date_from > '".$df."' AND date_to <= '".$dt."') OR
                          (date_from = '".$df."' AND date_to = '".$dt."'))";  
            
            $sql .= " AND worktime_type='".$entry_type."'";
            $sql .= " AND idperson ='".$b['Mitarbeiter']."'";
            
            return $sql;
        }         
        
        public function checkTimeForExistence($b=array(),$bSerie=false,$bCollision=false) 
        {
            $entry_type = "";
            $df = "";
            $dt = "";
            $aExist = array();
            
            if(count($b) > 0) {
            
                $date_from = new Zend_Date($b['date_from'],'dd.MM.yyyy HH:mm:ss');
                $df = $date_from->toString(MYSQL_DATETIME_FORMAT);
                $date_to = new Zend_Date($b['date_to'],'dd.MM.yyyy HH:mm:ss');
                $dt = $date_to->toString(MYSQL_DATETIME_FORMAT);
                
                $entry_type = "";
                if($b['modus']=="wt")
                    $entry_type = "worktime";
                else if($b['modus']=="av")
                    $entry_type = "availability";
                else
                    $entry_type = "worktime";
                    
                $entry_for = "";
                if(isset($b['modus_for']))
                    $entry_for = $b['modus_for'];                    
                
                if(!$bSerie) {

                    if($entry_type == "availability" && !$this->isEventInTheFuture($df) && !$this->isEventInTheFuture($dt) && $entry_for=="wt") {
                    
                        $aExist = array("success"=>true,
                                        "numEntries"=>1,
                                        "date_from"=>$df,
                                        "date_to"=>$dt,
                                        "idperson"=>$b['Mitarbeiter']);                         

                    } else {
                    
                        $sql = $this->generateTimeExistenceQuery($this->table_names[1],$df,$dt,$entry_type,$b,$bCollision);
                            
                        $stmt = $this->db->query($sql);
                        $c = $stmt->fetch();
                        $numEntries = $c['ne'];                    
                                        
                        $aExist = array("success"=>$numEntries ? true:false,
                                        "numEntries"=>$numEntries,
                                        "date_from"=>$df,
                                        "date_to"=>$dt,
                                        "idperson"=>$b['Mitarbeiter']);                    
                        
                    }
                                    

                } else if($bSerie) {

                    $date_serie = new Zend_Date($this->addEndtimeInHmToDate($b['expire_date']),'dd.MM.yyyy HH:mm:ss');
                    $date_serie->set(23,Zend_Date::HOUR);
                    $date_serie->set(59,Zend_Date::MINUTE);
                    $date_serie->set(59,Zend_Date::SECOND);

                    $unix_timestamp_ds = $this->get_unixtime_from_mysql_dt($date_serie->toString(MYSQL_DATETIME_FORMAT));
                    $day_steps = 0;
                    
                    $aExist = array("success"=>true,
                                    "numEntries"=>0,
                                    "idperson"=>$b['Mitarbeiter']);                    
                    
                    switch($b['once_serie']) {
                        case 'x':
                            $day_steps = $b['x_days'];
                            break;
                        case 's':
                        default:
                            $day_steps = 7;
                            break;                        
                    }
                    
                    $numEntries = 0;
                    
                    $bReached = false;
                    
                    while(!$bReached) {

                        $df = $date_from->toString(MYSQL_DATETIME_FORMAT);
                        $dt = $date_to->toString(MYSQL_DATETIME_FORMAT);
                        
                        if($this->get_unixtime_from_mysql_dt($df) > $unix_timestamp_ds) {
                            $bReached |= true;
                            continue;
                        }                        
                        
                        $sql = $this->generateTimeExistenceQuery($this->table_names[1],$df,$dt,$entry_type,$b,$bCollision);
                        //print $sql;
                            
                        $stmt = $this->db->query($sql);
                        $c = $stmt->fetch();
                        $numEntries = $c['ne'];
                        
                        if(!$numEntries && !$bCollision) {
                                        
                            $aExist = array("success"=>false,
                                            "numEntries"=>0,
                                            "date_from"=>$df,
                                            "date_to"=>$dt,
                                            "idperson"=>$b['Mitarbeiter']);
                            break;
                        } else if($numEntries && $bCollision) {
                            /* here we one or more collisions
                               lets break and leave the loop */
                            $aExist = array("success"=>true,
                                            "numEntries"=>$numEntries,
                                            "date_from"=>$df,
                                            "date_to"=>$dt,
                                            "idperson"=>$b['Mitarbeiter']);                            
                            break;
                        } 

                        $date_from->addDay($day_steps);
                        $date_to->addDay($day_steps);
                    }
                    
                    if(!$numEntries && $bCollision) {
                        $aExist = array("success"=>false,
                                        "numEntries"=>$numEntries,
                                        "date_from"=>$df,
                                        "date_to"=>$dt,
                                        "idperson"=>$b['Mitarbeiter']);                            
                    }
                }
            }
            return $aExist;
        }        
        
        protected function setTableLastAutoincrementMaxId($table) 
        {
            $sql = "";
            
            if(strlen($table)>0) 
            {
                $sql = sprintf("SELECT max(id) as id FROM %s",$table);
                $this->db->query($sql);
                $stmt = $this->db->query($sql);                
                $c = $stmt->fetch();
                $id = strval($c['id']);
                
                // according to mysql reference manual lowest auto inc val should be 1
                if($id==0)
                    $id=1;
                    
                $sql = sprintf("ALTER TABLE %s AUTO_INCREMENT=%d",$table,$id);
                $this->db->query($sql);
            }
            return;
        }
        
        protected function isEventInTheFuture($str_date_from) 
        {
            $bIsInTheFuture = false; 
            if(strlen($str_date_from)>0) {
                $unix_time_now = (int)$this->get_unixtime_from_mysql_dt(sprintf("%04s-%02s-%02s %02s:%02s:%02s",
                                 date("Y"),date("m"),date("d"),date("H"),date("i"),date("s")));
                $unix_time_event_start = (int)$this->get_unixtime_from_mysql_dt($str_date_from);
                $diff = $unix_time_now - $unix_time_event_start;
                if($diff<0)
                    $bIsInTheFuture|=true;
            }
            return $bIsInTheFuture;
        }

        public function deleteWorktime($id) 
        {
            $bIsOnlyOneEntry = false;
            
            $sql = sprintf("SELECT idworktime_series AS idwts, worktime_type AS wtt, date_from AS dtf, date_to AS dtt, idperson AS idp 
                            FROM %s WHERE id='%d'",$this->table_names[1],$id);
            $stmt = $this->db->query($sql);                
            $c = $stmt->fetch();
            $idwts = strval($c['idwts']);
            $worktime_type = (string)$c['wtt'];
            
            if(strcmp($worktime_type,"availability")==0) {
            
                /* type is availability so determine if the date is in the future 
                   if so then delete the worktime created for this availability 
                   by doing 1-level function recursion */
                   
                if($this->isEventInTheFuture($c['dtf'])) {
                    $sql = $this->generateTimeExistenceQueryForDeletion($this->table_names[1],$c['dtf'],$c['dtt'],'worktime',array('Mitarbeiter'=>$c['idp']));
                    $stmt0 = $this->db->query($sql);
                    $n0 = $stmt0->fetch();
                    if((int)strval($n0['ne'])) {
                        $sql = $this->getAvailabilityWorktimesQueryForDeletion($this->table_names[1],$c['dtf'],$c['dtt'],'worktime',array('Mitarbeiter'=>$c['idp']));
                        $stmt0 = $this->db->query($sql);
                        $n0 = $stmt0->fetchAll();
                        foreach($n0 as $subArray) {
                        
                            /* now do a recursive 1-level call to our delete function to 
                               delete the worktime entry associated with this availability */
                               
                            $this->deleteWorktime($subArray['id']);
                        }
                    }
                }
            }
            
            if($idwts > 0) {
            
                /* if there is only one worktime event left to delete and we got a serie 
                   lets delete the entry for a serie from worktime_series table too */
                   
                $sql = sprintf("SELECT count(*) as cs FROM %s WHERE idworktime_series='%d'",$this->table_names[1],$idwts);
                $this->db->query($sql);
                $stmt = $this->db->query($sql);                
                $c = $stmt->fetch();
                $count = strval($c['cs']);
                
                if(1==$count) {
                    $bIsOnlyOneEntry = true;
                }
            }
            
            $sql = "DELETE FROM ".$this->table_names[1]." WHERE id='".$id."'";
            $this->db->query($sql);
            $this->setTableLastAutoincrementMaxId($this->table_names[1]);
            
            if($bIsOnlyOneEntry && $idwts) {
            
                /* delete the serie entry for the deleted worktime */
                
                $sql = "DELETE FROM ".$this->table_names[2]." WHERE id='".$idwts."'";
                $this->db->query($sql);
                $this->setTableLastAutoincrementMaxId($this->table_names[2]);             
            }
        }

        public function deleteWorktimeSerie($sid)
        {
            $worktime_type = "";
            $sql = "SELECT DISTINCT worktime_type AS wtt FROM ".$this->table_names[1]." WHERE idworktime_series='".$sid."'";
            $stmt = $this->db->query($sql);
            $n = $stmt->fetchAll();
            foreach($n as $n1)
                $worktime_type = $n1["wtt"];
            switch($worktime_type) {
                case "worktime": {
                    $sql = "DELETE FROM ".$this->table_names[1]." WHERE idworktime_series='".$sid."'";
                    $this->db->query($sql);
                    $this->setTableLastAutoincrementMaxId($this->table_names[1]);
                    $sql = "DELETE FROM ".$this->table_names[2]." WHERE id='".$sid."'";
                    $this->db->query($sql);
                    $this->setTableLastAutoincrementMaxId($this->table_names[2]);                
                }
                break;
                case "availability": {
                    $sql = "SELECT c.* FROM ".$this->table_names[1]." AS c 
                            WHERE c.idworktime_series='".$sid."'";
                    $stmt = $this->db->query($sql);
                    $n = array();
                    $n = $stmt->fetchAll();
                    foreach($n as $subArray) {
                        $this->deleteWorktime($subArray["id"]);
                    }
                }
                break;
                default:
                break;
            }
            return;
        }
	
        public function getWorkTime() 
        {
            //$select = $this->db->select()->from($this->table_names[1]);
            $select = $this->db->select()
                      ->from(array('a' => $this->table_names[1]))
                      ->join(array('b' => $this->table_names[0]),'a.idperson = b.id',array('forename','surname'))
                      ->where("b.person_type='Mitarbeiter'")
                      ->order("b.surname ASC")
                      ->order("b.forename ASC");
            $result = $this->db->fetchAll($select);
            return $result;    
        }

        public function find($id)
        {
            $select = $this->db->select()
                      ->from($this->table_names[1])
                      ->where('id = ?', $id);
            return $this->db->fetchRow($select);
        }

        protected function convertToMySQLDate($s) {
            // assumed YYYY-MM-DDTHH:mm:ss.000Z
            $s0 = !(strlen($s) >= 10) ? "" : substr($s,0,10);
            return $s0;
        }

        public function getWorkTimes($from,$to,$modus='wt',$pid='all')
        {
            $entry_type = ($modus!="av") ? "worktime":"availability";
            /*if($pid=='a') {
                $select = $this->db->select()
                          ->from($this->table_names[1])
                          ->where('date_from >= ?', $this->convertToMySQLDate($from)." 00:00:00")
                          //->where('date_to < ?', $this->convertToMySQLDate($to)." 23:59:59")
                          ->where('date_from < ?', $this->convertToMySQLDate($to)." 23:59:59")
                          ->where('worktime_type = ?', $entry_type)
                          ->order('date_from ASC');
            } else {
                $pid=(int)strval($pid);
                $select = $this->db->select()
                          ->from($this->table_names[1])
                          ->where('date_from >= ?', $this->convertToMySQLDate($from)." 00:00:00")
                          //->where('date_to < ?', $this->convertToMySQLDate($to)." 23:59:59")
                          ->where('date_from < ?', $this->convertToMySQLDate($to)." 23:59:59")
                          ->where('idperson = ?', $pid)
                          ->where('worktime_type = ?', $entry_type)                          
                          ->order('date_from ASC');    
            }*/
            /*print $modus." ".$entry_type." ".$select;
            exit;*/
			$sql = "SELECT a.* FROM ".$this->table_names[1]." AS a 
					JOIN ".$this->table_names[0]." AS b ON (a.idperson = b.id)
					WHERE a.date_from >= '".$this->convertToMySQLDate($from)." 00:00:00"."' 
					AND a.date_from < '".$this->convertToMySQLDate($to)." 23:59:59"."' 
					AND (b.expire_date is null OR b.expire_date >= now()) 
					AND a.worktime_type = '".$entry_type."'".($pid!='all' ? " AND idperson = '".strval($pid)."'":"")." 
					ORDER BY date_from ASC";

			$stmt = $this->db->query($sql);
            $c = $stmt->fetchAll();
			return $c;

            //return $this->db->fetchAll($select);
        }

        public function updateWorkTimeOnEvent($a=array()) 
        {
            if(is_array($a))
                if(count($a)) 
                {
                    $date_from = new Zend_Date($a['date_from'],'dd.MM.yyyy HH:mm');
                    //$date_from = $this->swapDayMonth($date_from);
                    $df = $date_from->toString(MYSQL_DATETIME_FORMAT);
		
                    $date_to = new Zend_Date($a['date_to'],'dd.MM.yyyy HH:mm');
                            //$date_to = $this->swapDayMonth($date_to);
                    $dt = $date_to->toString(MYSQL_DATETIME_FORMAT);

                    $sql = "UPDATE %s SET date_from='%s',
                                          date_to='%s' 

                                          WHERE id='%d'";

                    $sql = sprintf($sql,$this->table_names[1],$df,$dt,$a['id']);
                    $this->db->query($sql);
                }
            return;
        }

        public function get_unixtime_from_mysql_dt($str) 
        {
            $time=0;
            if(strlen($str)) 
            {
                $year = (int)strval(substr($str,0,4));
                $month = (int)strval(substr($str,5,2));
                $day = (int)strval(substr($str,8,2));
                $hour = (int)strval(substr($str,11,2));
                $minute = (int)strval(substr($str,14,2));
                $second = (int)strval(substr($str,17,2));
                $time = mktime($hour,$minute,$second,$month,$day,$year);
            }
            return $time;
        }

        public function get_unix_time_in($t,$flag="second") 
        {
            $result = 0.0;          
            if($t==0)
                return 0.0;            
            switch($flag) 
            {
                case "minute":
                    $result=(double)$t/60.0;
                    break;
                case "hour":
                    $result=(double)$t/(60.0*60.0);
                    break;
                case "day":
                    $result=(double)$t/(60.0*60.0*24.0);
                    break;
                case "month":
                    $result=(double)$t/(60.0*60.0*24.0*30.0);
                    break;
                case "year":
                    $result=(double)$t/(60.0*60.0*24.0*30.0*12.0);
                    break;
                case "second":
                default:
                    $result=(double)$t;
                    break;
            }
            return $result;
        }
        
        public function getWorktimeSerie($id) 
        {
            $sql = ""; 
            $a = array('serie'=>false,
                       'idws'=>0,
                       'idw'=>$id,
                       'exist'=>false);
            if($id) {
                $sql = sprintf("SELECT count(*) AS ne FROM  %s WHERE id='%d'",$this->table_names[1],$id);
                $this->db->query($sql);
                $stmt = $this->db->query($sql);                
                $c = $stmt->fetch();
                $count = (int)strval($c['ne']);
                if($count) {
                    $sql = sprintf("SELECT a.idworktime_series AS idws 
                                    FROM %s AS a
                                    JOIN %s AS b ON (a.idworktime_series = b.id) 
                                    WHERE a.id='%d'",$this->table_names[1],$this->table_names[2],$id);
                    $this->db->query($sql);
                    $stmt = $this->db->query($sql);                
                    $c = $stmt->fetch();
                    $idws = (int)strval($c['idws']);
                    $a = array('serie'=>true,
                               'idws'=>$idws,
                               'idw'=>$id,
                               'exist'=>true);                    
                    if(!$idws) {
                        $a['serie'] = false;
                    }
                }                
            }
            return $a;
        }
        
        public function getWorktimeDataById($id,$method=0) 
        {
            $sql = "";
            $b = array();
            if($id) {
               $sql = sprintf("SELECT * FROM %s WHERE id='%d'",$this->table_names[1],$id);
               $a = $this->getWorktimeSerie($id);
               $idws = (int)strval($a['idws']);
               if(0<$idws && $a['exist'] && $method!=0) {
                   $sql = (string)"SELECT c.* FROM %s AS c WHERE c.idworktime_series='%d'"; 
                   switch($method) {
                       case 1:
                           // do nothing
                           break;                   
                       case 2:
                           $sql .= sprintf(" AND c.id<='%d'",$id);
                           break;                   
                       case 3:
                           $sql .= sprintf(" AND c.id>='%d'",$id);
                           break;
                       default:
                           break;
                   }
                   $sql = sprintf($sql." ORDER BY c.id ASC",$this->table_names[1],$idws);
               }
               $this->db->query($sql);
               $stmt = $this->db->query($sql);                
               while($c = $stmt->fetch())
                   $b[] = $c;
            }
            return $b;
        }
        
        public function generateWorktimeFromAvailability($ida,$c=array()) 
        {
            $sql = ""; 
            $a = array("success"=>false,"errors"=>"");
            if(!!is_array($c)) {
                if(count($c)>0) {
                    $b = $this->getWorktimeDataById($ida,$c["generation_method"]);
                    $bCollid = false;
                    /* lets check for collision with existent worktimes first */
                    foreach($b as $subArray) {
                        $d = $subArray;
                        $d["Mitarbeiter"] = $d["idperson"];
                        //unset($d["idperson"]);
                        $sql = $this->generateTimeExistenceQuery($this->table_names[1],$subArray['date_from'],$subArray['date_to'],"worktime",$d,true);
                        $this->db->query($sql);
                        $stmt = $this->db->query($sql);                
                        $e = $stmt->fetch();
                        if(strval($e['ne'])!=0) {
                            $a["errors"] = sprintf("Es wurde eine Kollision mit einer bereits eingtragenen Arbeitszeit (%s - %s) festgestellt. Bitte korrigieren Sie diese zuerst.",
                            $this->get_worker_dt_from_mysql_dt($subArray['date_from']),$this->get_worker_dt_from_mysql_dt($subArray['date_to']));
                            $bCollid = true;
                            break;
                        }
                    }
                    if(!$bCollid) {
                        /* No collisions ? lets generate worktimes */
                        $a["success"] = true;
                        $bSerie = (count($b) > 1) ? true:false;
                        $idSerie = 0;
                        $entry_type = "worktime";
                        if($bSerie) {
                            // lets get a valid serial id from worktime_series table
                            $sql = "INSERT INTO ".$this->table_names[2]." (date_create) values(now());";
                            $this->db->query($sql);

                            $sql = "SELECT max(id) as id FROM ".$this->table_names[2];
                            $stmt = $this->db->query($sql);
                            $ca = $stmt->fetch();
                            $idSerie = $ca['id'];
                        }
                        foreach($b as $subArray) {
                            $sql = "INSERT INTO ".$this->table_names[1]." (idperson,date_from,date_to,description,".($bSerie ? "idworktime_series,":"")."worktime_type,date_create) 
                                                    values('".$subArray['idperson']."',
                                                           '".$subArray['date_from']."',
                                                           '".$subArray['date_to']."',
                                                           '".mysql_real_escape_string($c['description'])."',
                                                           ".($bSerie ? ("'".$idSerie."',"):(""))."
                                                           '".$entry_type."',                                                           
                                                           now());";
                                                       
                            $this->db->query($sql);                            
                        }
                    }
                }
            }
            return $a;
        }
        
        public function getAlternatesForWorktime($idw) 
        {
            $a = array(); $sql = "";
            $idw = (int)strval($idw);
            if($idw) {
                $sql = sprintf("SELECT * FROM %s WHERE id='%d'",$this->table_names[1],$idw);
                $stmt = $this->db->query($sql);
                $c = $stmt->fetch();
                if(count($c)) {
                    /* get alternate availabilities */             
                    $sql = "SELECT a.* FROM ".$this->table_names[1]." AS a 
                            INNER JOIN ".$this->table_names[0]." AS b ON (a.idperson = b.id) 
                            WHERE ((a.date_from <= '".$c['date_from']."' AND a.date_to > '".$c['date_to']."') OR
                                  (a.date_from < '".$c['date_from']."' AND a.date_to >= '".$c['date_to']."') OR
                                  (a.date_from = '".$c['date_from']."' AND a.date_to = '".$c['date_to']."')) 
                                  AND a.worktime_type = 'availability' 
                                  AND a.idperson!= '".$c['idperson']."' 
                                  ORDER BY b.surname,b.forename ASC";
                    $stmt = $this->db->query($sql);
                    $e = array();
                    while($ee = $stmt->fetch())
                        $e[] = $ee;
                    foreach($e as $subArray) {
                        /* check alternates for collisions with already booked worktimes */
                        $subArray['Mitarbeiter'] = $subArray['idperson'];
                        $sql = $this->generateTimeExistenceQuery($this->table_names[1],$c['date_from'],$c['date_to'],'worktime',$subArray,true);
                        $stmt = $this->db->query($sql);
                        $f = $stmt->fetch();
                        $isBooked = (int)strval($f['ne']);
                        if(!$isBooked) {
                            $aPerson = $this->getWorkerById($subArray['idperson']);
                            $sPerson = $aPerson['forename']." ".$aPerson['surname'];
                            $a[] = array('id'=>$subArray['idperson'],
                                         'name'=>$sPerson);
                        }
                    }
                }
                     
            }
            return $a;
        }
        
        public function bookAlternative($c) 
        {   
            $sql = sprintf("SELECT COUNT(*) as cs FROM %s WHERE id='%d'",$this->table_names[1],(int)strval($c['id']));
            $stmt = $this->db->query($sql);
            $f = $stmt->fetch();
            $isExistent = (int)strval($f['cs']);
            if($isExistent) {
                $sql = sprintf("UPDATE %s SET idperson='%d' WHERE id='%d'",$this->table_names[1],(int)strval($c['alternative_worker']),(int)strval($c['id']));
                $this->db->query($sql);
            }
            return;
        }
	
    }

?>
